package com.sq.partner.manager.service.impl;

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;

import com.sq.partner.manager.common.Config;
import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.common.DictCache;
import com.sq.partner.manager.mybatis.model.DictDto;
import com.sq.partner.manager.util.FreeMarkerUtil;
import com.sq.partner.manager.util.HttpConnectorUtil;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.Node;
import org.springframework.stereotype.Service;

import com.sq.partner.manager.common.Result;
import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.dao.CarPushDataQueryDao;
import com.sq.partner.manager.dao.ICoreDeptDao;
import com.sq.partner.manager.dao.IGatewayAgentInfoMtDao;
import com.sq.partner.manager.dao.IGatewayAgentInfoRelaDao;
import com.sq.partner.manager.dao.IGatewayChannelAppDao;
import com.sq.partner.manager.dao.IGatewayChannelInfoConfigDao;
import com.sq.partner.manager.dao.IGatewaySubChannelDao;
import com.sq.partner.manager.mybatis.model.CarPushDataQuery;
import com.sq.partner.manager.mybatis.model.CoreDept;
import com.sq.partner.manager.mybatis.model.GatewayChannelApp;
import com.sq.partner.manager.mybatis.model.GatewaySubChannel;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.TradAgentInfo;
import com.sq.partner.manager.mybatis.model.TradAgentInfoRela;
import com.sq.partner.manager.service.CarPushDataQueryService;
import com.sq.partner.manager.service.IGatewayClearCacheManager;
import com.sq.partner.manager.util.ExcelUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.RedisUtil;
import com.sq.partner.manager.util.StrUtil;

@Service
public class CarPushDataQueryServiceImpl implements CarPushDataQueryService {
	private static Logger logger = LogUtil.getLog(CarPushDataQueryServiceImpl.class); 
	@Resource
	CarPushDataQueryDao carPushDataQueryDao;
	@Resource
	IGatewayChannelInfoConfigDao gatewayChannelInfoConfigDao;
	@Resource
	private IGatewayChannelInfoConfigDao channelInfoConfigDao;
	@Resource
	private IGatewaySubChannelDao subChannelDao;
	@Resource
	IGatewayClearCacheManager gatewayClearCacheManager;
	@Resource
	private IGatewayAgentInfoMtDao agentInfoMtDao;
	@Resource
	IGatewayChannelAppDao gatewayChannelAppDao;
	@Resource
	private IGatewaySubChannelDao subChannelInfoDao;
	@Resource
	IGatewayAgentInfoRelaDao agentInfoRelaDao;
	@Resource
	ICoreDeptDao deptDao;
	@Override
	public List<CarPushDataQuery> pushDataQuery(String carVin) throws Exception {
		
		List<CarPushDataQuery> pushDataList = new ArrayList<CarPushDataQuery>();
		// 查询大众数据如果为空则查询东风数据 如果不为空则根据经销商代码查询服务代码
		CarPushDataQuery carPushDataQueryData = carPushDataQueryDao.queryCarDataInfoForDZ(carVin);
		if (null != carPushDataQueryData) {
			// 根据经销商代码查询服务代码
			if ("01,03".contains(carPushDataQueryData.getCarBrand())) {
				List<CarPushDataQuery> list = carPushDataQueryDao.queryServiceCodeMapping(carPushDataQueryData.getDealerCode());
				if (null != list && list.size() > 0) {
					carPushDataQueryData.setServiceCode(list.get(0).getServiceCode());
				}
			} else {
				List<CarPushDataQuery> list = carPushDataQueryDao.queryServiceCodeDict(carPushDataQueryData.getDealerCode());
				if (null != list && list.size() > 0) {
					carPushDataQueryData.setServiceCode(list.get(0).getServiceCode());
				}
			}
			pushDataList.add(carPushDataQueryData);
		} else {
			// 查询东风数据
			List<CarPushDataQuery> pushDataInfoForDF = carPushDataQueryDao.queryCarDataInfoForDF(carVin);
			if (null != pushDataInfoForDF && pushDataInfoForDF.size() > 0) {
				CarPushDataQuery carPushDataQuery = pushDataInfoForDF.get(0);
				for (int i = 0; i < pushDataInfoForDF.size(); i++) {
					if("0360".equals(pushDataInfoForDF.get(i).getProdNo()) && StrUtil.isEmpty(carPushDataQuery.getSyPolno())){
						carPushDataQuery.setSyPolno(pushDataInfoForDF.get(i).getDfPolno());
					}else if("0332".equals(pushDataInfoForDF.get(i).getProdNo()) && StrUtil.isEmpty(carPushDataQuery.getJqPolno())){
						carPushDataQuery.setJqPolno(pushDataInfoForDF.get(i).getDfPolno());
					}else{
						break;
					}
				}
				List<CarPushDataQuery> list = carPushDataQueryDao.queryServiceCodeMapping(carPushDataQuery.getDealerCode());
				if(null != list && list.size() > 0){
					carPushDataQuery.setServiceCode(list.get(0).getServiceCode());
				}
				carPushDataQuery.setCarBrand("04");
				pushDataList.add(carPushDataQuery);
			} else {
				return null;
			}
		}
		return pushDataList;
	}

	@Override
	public Result saveAgentMtInfo(String flag, GatewayUser user,TradAgentInfo agentInfo,String jqMax,String jqMin,String syMax,String syMin) throws Exception {
			GatewaySubChannel subChannelInfo = new GatewaySubChannel();
			subChannelInfo.setSubChannelCode(agentInfo.getBranch());
			subChannelInfo.setSubChannelName(agentInfo.getBranchName());
			subChannelInfo.setParentChannelCode(agentInfo.getChannelCode());
			subChannelInfo.setBranch(agentInfo.getBranch());
			subChannelInfo.setBranchName(agentInfo.getBranchName());
			subChannelInfo.setFkChannelCode(agentInfo.getChannelCode());
			subChannelInfo.setStatus("1");
			subChannelInfo.setOperUser(user.getUserid());
			subChannelInfo.setOperTime(new Date());
			Map<String, String> map=new HashMap<String, String>();
			map.put("subChannelCode", subChannelInfo.getSubChannelCode());
			map.put("chanelCode", subChannelInfo.getFkChannelCode());
			map.put("jqMax",jqMax);
			map.put("jqMin",jqMin);
			map.put("syMax",syMax);
			map.put("syMin",syMin);
			map.put("flag","'"+subChannelInfo.getParentChannelCode().concat(agentInfo.getBranch())+"'");
			Map<String, String> resultMap = agentInfoValidation(agentInfo);
			if (!"0000".equals(resultMap.get("result"))){
				return Result.error(resultMap.get("result"));
			}
			if("save".equals(flag)){
				if(null!=subChannelDao.querySubChannelInfoByCondition(map)){
					return Result.error("该网点代码已经存在,请更换");
				}
				subChannelDao.save(subChannelInfo);
			}else if("update".equals(flag)){
				subChannelDao.update("updateGatewaySubChannel", subChannelInfo);
			}
		agentInfo.setSubChannelCode(subChannelInfo.getSubChannelCode());
		agentInfo.setOperTime(new Date());
		agentInfo.setOperUser(user.getUserid());
		if("save".equals(flag)){
			if(agentInfoMtDao.addAgentInfo(agentInfo)){
				List<TradAgentInfoRela> relaList = getAddOrUpdateAgentRela(map,user);
				agentInfoRelaDao.insertIntoAgentRealForCar(relaList);
				clearAllCacheByChannelCode(agentInfo.getChannelCode());
				return Result.success("添加成功！");
			}
			return Result.error("添加失败！");
		}else if("update".equals(flag)){
			if(agentInfoMtDao.updateAgentInfo(agentInfo)){
				List<TradAgentInfoRela> relaList = getAddOrUpdateAgentRela(map,user);
				agentInfoRelaDao.updateAlreadyExistsCarInfo(relaList);
				clearAllCacheByChannelCode(agentInfo.getChannelCode());
				return Result.success("更新成功！");
			}
			return Result.error("更新失败！");
		}else{
			return Result.error("操作失败，系统检查出非法数据传入，请检查！");
		}
	}
	
	public List<TradAgentInfoRela> getAddOrUpdateAgentRela(Map<String,String> map,GatewayUser user)throws Exception{
		List<TradAgentInfo> carAgentInfo = agentInfoMtDao.selectAddCarAgentInfo(map);
		List<TradAgentInfoRela> relaList = new ArrayList<TradAgentInfoRela>();
		if(null != carAgentInfo && carAgentInfo.size() > 0){
			for (TradAgentInfo tradAgentInfo : carAgentInfo) {
				TradAgentInfoRela agentInfoRela = new TradAgentInfoRela();
				agentInfoRela.setAgentInfoId(tradAgentInfo.getId());;
				agentInfoRela.setInsuPlanNo(tradAgentInfo.getPlanCode());
				agentInfoRela.setOperUser(user.getUserid());
				agentInfoRela.setOperTime(new Date());
				if("0332".equals(tradAgentInfo.getProdType())){
					if(StrUtil.isNotEmpty(map.get("jqMax"))){
						agentInfoRela.setNfeeProdMax(new BigDecimal(map.get("jqMax")));
					}
					if(StrUtil.isNotEmpty(map.get("jqMin"))){
						agentInfoRela.setNfeeProdMin(new BigDecimal(map.get("jqMin")));
					}
				}
				if("0360".equals(tradAgentInfo.getProdType())){
					if(StrUtil.isNotEmpty(map.get("syMax"))){
						agentInfoRela.setNfeeProdMax(new BigDecimal(map.get("syMax")));
					}
					if(StrUtil.isNotEmpty(map.get("syMin"))){
						agentInfoRela.setNfeeProdMin(new BigDecimal(map.get("syMin")));
					}
				}
				relaList.add(agentInfoRela);
			}
		}
		return relaList;
	}
	//合作伙伴配置缓存管理公共方法
	public void clearAllCacheByChannelCode(String channelCode) throws Exception {
		//查询应用by：channelCode
		List<GatewayChannelApp> appLs = gatewayChannelAppDao.queryChannelAppInfoListByChannelCode(channelCode);
		if(null!=appLs && appLs.size()>0){
			for(GatewayChannelApp tem :appLs){
				gatewayClearCacheManager.clearAllCache(tem.getAppId());
			}
		}
	}

	@Override
	public Page<TradAgentInfo> queryCarAgentPage(Page<TradAgentInfo> page)throws Exception {
		Page<TradAgentInfo> carAgentInfo = agentInfoMtDao.selectCarAgentInfo(page);
		List<TradAgentInfo> data = carAgentInfo.getData();
		if(null!=data&&data.size()>0){
			String id = "";
			for (TradAgentInfo tradAgentInfo : data) {
				id+="'"+tradAgentInfo.getId()+"',";
			}
			List<TradAgentInfoRela> agentRela = agentInfoRelaDao.selectAgentRela(id.substring(0, id.length()-1));
			for (int i = 0; i < data.size(); i++) {
				for (int j = 0; j < agentRela.size(); j++) {
					if(data.get(i).getId().equals(agentRela.get(j).getAgentInfoId())){
						if("0360".equals(agentRela.get(j).getProdType())){
							data.get(i).setSyMax(String.valueOf(agentRela.get(j).getUpperLimit()));
							data.get(i).setSyMin(String.valueOf(agentRela.get(j).getLowerLimit()));
						}
						if("0332".equals(agentRela.get(j).getProdType())){
							data.get(i).setJqMax(String.valueOf(agentRela.get(j).getUpperLimit()));
							data.get(i).setJqMin(String.valueOf(agentRela.get(j).getLowerLimit()));
						}
					}
				}
			}
		}
		return carAgentInfo;
	}

	@SuppressWarnings("unchecked")
	@Override
	public synchronized void batchImportAgents(InputStream inputStream, HttpSession session,GatewayUser user)throws Exception {
		HSSFWorkbook workBook = null;
		List<TradAgentInfo> list = null;
		try{
			workBook = new HSSFWorkbook(inputStream);
			list = new ArrayList<TradAgentInfo>();
		}catch(Exception e){
			logger.info("上传失败，上传文件内容有误",e);
			throw new AppException("上传失败，上传文件内容有误!");
		}
		int sheets=workBook.getNumberOfSheets();
		HSSFSheet sheet1 = null;
		boolean flag=false;
		if(sheets!=2){
			throw new AppException("上传失败，上传excel文件模板非最新模板");
		}else{
			sheet1 = workBook.getSheetAt(1);
			String sheetName1 = workBook.getSheetName(1);
			if(StrUtil.isNotEmpty(sheetName1) && sheetName1.contains("总对总销售渠道信息")){
				flag=true;
			}
		}
		int sheet1LastRowNum = sheet1.getLastRowNum();
		if(sheet1LastRowNum < 2 && flag){
			throw new AppException("上传失败，上传excel文件内容为空或模板非最新模板");
		}else if(sheet1LastRowNum > 101){
			throw new AppException("为了用户体验，一次最多只能导入100条数据");
		}
		Map<String, Object> batchImportMap = batchImportRuleCheck(sheet1,user);
		list = (List<TradAgentInfo>)batchImportMap.get("AGENTLIST");
		StringBuffer errorBuf = new StringBuffer();
		Map<String,Integer> excelIndexChannelInfo = new HashMap<String,Integer>();//用于判断合作伙伴是否有效
		List<TradAgentInfoRela> addAgentInfoRelaList = null;
		List<TradAgentInfoRela> updateAgentInfoRelaList = null;
		List<TradAgentInfo> allChannelInfo = null;
		int errorCount = 0;
		//查询IN条件拼接的字符串
		String inSubDBTemConcatStr="";
		String inChannelDBTemConcatStr="";//合作伙伴代码拼接
		//提高性能算法从DB检查次数
		int totalRowCounts = sheet1LastRowNum+1;
		int checkFromDBCount=0;
		//每300条从数据库中查询是否满足条件
		if(totalRowCounts/300<0){
			checkFromDBCount=1;
		}else if(totalRowCounts/300>0){
			checkFromDBCount = totalRowCounts/300;
		}
		List<Integer> checkDBCountLs = new  ArrayList<Integer>(checkFromDBCount);
		for(int i=1;i<=checkFromDBCount;i++){
			checkDBCountLs.add(Integer.valueOf(i*300));
		}
		//校验合作伙伴
		allChannelInfo = agentInfoMtDao.searchExistChannelInfoFromDB(user.getDepartmentStr());
		if(null == allChannelInfo || allChannelInfo.size() == 0){
			throw new AppException("上传失败:<br>当前登陆用户权限下不存在任何合作伙伴信息，请先申请权限后后导入<br>");
		}
		Map<String, String> map = new HashMap<String, String>();
		Map<String, TradAgentInfo> channelMap = new HashMap<String, TradAgentInfo>();
		List<TradAgentInfo> alreadyExistsAgentInfo = new ArrayList<TradAgentInfo>();
		List<TradAgentInfo> addAgentInfoList = new ArrayList<TradAgentInfo>();//execl全部代理信息
		if (null != list && list.size() > 0) {
			addAgentInfoList.addAll(list);
			for (int i = 0; i < list.size(); i++) {
				channelMap.put(list.get(i).getChannelCode() + list.get(i).getSubChannelCode(), list.get(i));
				if (checkDBCountLs.contains(i)) {
					List<TradAgentInfo> existsList = agentInfoMtDao.searchExistSubBranchCodeInfoFromDB(map);
					if (null != existsList && existsList.size() > 0) {
						alreadyExistsAgentInfo.addAll(existsList);
					}
					inSubDBTemConcatStr = "";
					map.clear();
				}
				inSubDBTemConcatStr += "'" + list.get(i).getChannelCode() + list.get(i).getSubChannelCode() + "',";
				map.put("flag",inSubDBTemConcatStr.substring(0,inSubDBTemConcatStr.length() - 1));
				if (!(inChannelDBTemConcatStr.contains(list.get(i).getChannelCode()))) {
					inChannelDBTemConcatStr += list.get(i).getChannelCode() + ",";
				}
				excelIndexChannelInfo.put(list.get(i).getChannelCode(), list.get(i).getExcelRowIndex());
			}
			List<String> temChannelLs = new ArrayList<String>();
			for (TradAgentInfo tem : allChannelInfo) {
				temChannelLs.add(tem.getChannelCode());
			}
			String[] StrArray = inChannelDBTemConcatStr.substring(0,inChannelDBTemConcatStr.length() - 1).split(",");
			for (String tem : StrArray) {
				if (errorCount > 9) {
					excelIndexChannelInfo.clear();
					throw new AppException("上传失败:<br>" + errorBuf.toString());
				}
				if (!temChannelLs.contains(tem)) {
					errorBuf.append("第 " + excelIndexChannelInfo.get(tem)+ "行的合作伙伴代码 " + tem + " 不存在或没有相关权限，请全列检查修改<br>");
					errorCount++;
				}
			}
		}
		//若有错误提示信息，则提示
		if(StrUtil.isNotEmpty(errorBuf.toString())){
			throw new AppException("上传失败:<br>"+errorBuf.toString());
		}
		//最后一次检查DB
		if (StrUtil.isNotEmpty(inSubDBTemConcatStr)){
			// 最后一次DB检查数据库
			List<TradAgentInfo> existsAgent = agentInfoMtDao.searchExistSubBranchCodeInfoFromDB(map);
			alreadyExistsAgentInfo.addAll(existsAgent);
			inSubDBTemConcatStr = "";
			map.clear();
		}
		//销管校验专用异常参数
		String errorResult = "";
		int errorConunt = 0;
		for (int i = 0; i < list.size(); i++) {
			//循环请求交易系统，校验数据合法性
			Map<String, String> resultMap = agentInfoValidation(list.get(i));
			if (!"0000".equals(resultMap.get("result"))){
				errorResult += "第"+(i+3)+"条数据中："+ resultMap.get("result") + "</br>";
				errorConunt += 1;
			}
		}
		if (errorConunt > 0){
			throw new AppException(errorResult);
		}
		/**取出所有已存在的用户*/
		List<TradAgentInfo>  updateAgents = null;
		List<TradAgentInfo>  updateAgentInfoRela = null;
		if(null != alreadyExistsAgentInfo && alreadyExistsAgentInfo.size()>0){
			for (int i = 0; i < alreadyExistsAgentInfo.size(); i++) {
				if(channelMap.get(alreadyExistsAgentInfo.get(i).getChannelCode()+alreadyExistsAgentInfo.get(i).getSubChannelCode()) != null){
					if(updateAgents == null){
						updateAgents = new ArrayList<TradAgentInfo>();
					}
					addAgentInfoList.remove(channelMap.get(alreadyExistsAgentInfo.get(i).getChannelCode()+alreadyExistsAgentInfo.get(i).getSubChannelCode()));
					/**设置id*/
					TradAgentInfo upinfo=channelMap.get(alreadyExistsAgentInfo.get(i).getChannelCode()+alreadyExistsAgentInfo.get(i).getSubChannelCode());
					upinfo.setId(alreadyExistsAgentInfo.get(i).getId());
					updateAgents.add(upinfo);
					//查询代理信息关联表拼接条件
					inSubDBTemConcatStr += "'" + alreadyExistsAgentInfo.get(i).getChannelCode() + alreadyExistsAgentInfo.get(i).getSubChannelCode() + "',";
					map.put("flag",inSubDBTemConcatStr.substring(0,inSubDBTemConcatStr.length() - 1));
					if(checkDBCountLs.contains(i)){
						List<TradAgentInfo> existsAgentRelaList = agentInfoMtDao.selectAddCarAgentInfo(map);
						if(null != existsAgentRelaList && existsAgentRelaList.size() > 0){
							if(null == updateAgentInfoRela){
								updateAgentInfoRela = new ArrayList<TradAgentInfo>();
							}
							updateAgentInfoRela.addAll(existsAgentRelaList);
						}
						inSubDBTemConcatStr="";
						map.clear();
					}
				}
			}
		}
		//最后一次检查DB
		if (StrUtil.isNotEmpty(inSubDBTemConcatStr)){
			// 最后一次DB检查数据库
			List<TradAgentInfo> existsAgent = agentInfoMtDao.selectAddCarAgentInfo(map);
			if(null != existsAgent && existsAgent.size() > 0){
				if(null == updateAgentInfoRela){
					updateAgentInfoRela = new ArrayList<TradAgentInfo>();
				}
				updateAgentInfoRela.addAll(existsAgent);
				inSubDBTemConcatStr = "";
				map.clear();
			}
		}
		//渠道子机构信息
		List<GatewaySubChannel> subChannelList = new ArrayList<GatewaySubChannel>();
		List<TradAgentInfo> agentInfoRelaList = null;
		if(null != addAgentInfoList && addAgentInfoList.size() > 0){
			for (int i = 0; i < addAgentInfoList.size(); i++) {
				GatewaySubChannel subChannelInfo = new GatewaySubChannel();
				subChannelInfo.setSubChannelCode(addAgentInfoList.get(i).getSubChannelCode());
				subChannelInfo.setSubChannelName(addAgentInfoList.get(i).getBranchName());
				subChannelInfo.setParentChannelCode(addAgentInfoList.get(i).getChannelCode());
				subChannelInfo.setBranch(addAgentInfoList.get(i).getBranch());
				subChannelInfo.setBranchName(addAgentInfoList.get(i).getBranchName());
				subChannelInfo.setFkChannelCode(addAgentInfoList.get(i).getChannelCode());
				subChannelInfo.setOperUser(user.getUserid());
				subChannelList.add(subChannelInfo);
				if(checkDBCountLs.contains(i)){
					List<TradAgentInfo> existsList = agentInfoMtDao.selectAddCarAgentInfo(map);
					if(null != existsList && existsList.size() > 0){
						if(null == agentInfoRelaList){
							agentInfoRelaList = new ArrayList<TradAgentInfo>();
						}
						agentInfoRelaList.addAll(existsList);
					}
					inSubDBTemConcatStr="";
					map.clear();
				}
				inSubDBTemConcatStr += "'"+addAgentInfoList.get(i).getChannelCode()+addAgentInfoList.get(i).getSubChannelCode()+"',";
				map.put("flag", inSubDBTemConcatStr.substring(0, inSubDBTemConcatStr.length()-1));
			}
		}
		//渠道子机构表
		if(null != subChannelList && subChannelList.size() > 0){
			subChannelInfoDao.addBatchMtSubBranchInfo(subChannelList);
		}
		Integer add=0;
		if(null != addAgentInfoList && addAgentInfoList.size() > 0){
			agentInfoMtDao.addBatchAgentInfo(addAgentInfoList);
			add=addAgentInfoList.size();
		}
		if(StrUtil.isNotEmpty(inSubDBTemConcatStr)){
			List<TradAgentInfo> existsAgent = agentInfoMtDao.selectAddCarAgentInfo(map);
			if(null != existsAgent && existsAgent.size() > 0){
				if(null == agentInfoRelaList){
					agentInfoRelaList = new ArrayList<TradAgentInfo>();
				}
				agentInfoRelaList.addAll(existsAgent);
			}
		}
		Integer update=0;
		if(null != updateAgents && updateAgents.size() > 0){
			agentInfoMtDao.updateBachAgentInfo(updateAgents);
			update=updateAgents.size();
		}
		logger.info("插入信息代理信息: 添加 "+add+"更新"+update);
		session.setAttribute("result", "销售信息导入成功: 添加 "+add+"条  更新"+update+"条");
		//代理信息关联表新增
		if(null != agentInfoRelaList && agentInfoRelaList.size() > 0){
			addAgentInfoRelaList = new ArrayList<TradAgentInfoRela>();
			for (TradAgentInfo tradAgentInfo : agentInfoRelaList) {
				TradAgentInfoRela agentInfoRela = new TradAgentInfoRela();
				agentInfoRela.setAgentInfoId(tradAgentInfo.getId());;
				agentInfoRela.setInsuPlanNo(tradAgentInfo.getPlanCode());
				agentInfoRela.setOperUser(user.getUserid());
				agentInfoRela.setOperTime(new Date());
				if("0332".equals(tradAgentInfo.getProdType())){
					if(null != batchImportMap.get(tradAgentInfo.getChannelCode().concat("JQMax"))){
						agentInfoRela.setNfeeProdMax(new BigDecimal((String)batchImportMap.get(tradAgentInfo.getChannelCode().concat("JQMax"))));
					}
					if(null != batchImportMap.get(tradAgentInfo.getChannelCode().concat("JQMin"))){
						agentInfoRela.setNfeeProdMin(new BigDecimal((String)batchImportMap.get(tradAgentInfo.getChannelCode().concat("JQMin"))));
					}
				}
				if("0360".equals(tradAgentInfo.getProdType())){
					if(null != batchImportMap.get(tradAgentInfo.getChannelCode().concat("SYMax"))){
						agentInfoRela.setNfeeProdMax(new BigDecimal((String)batchImportMap.get(tradAgentInfo.getChannelCode().concat("SYMax"))));
					}
					if(null != batchImportMap.get(tradAgentInfo.getChannelCode().concat("SYMin"))){
						agentInfoRela.setNfeeProdMin(new BigDecimal((String)batchImportMap.get(tradAgentInfo.getChannelCode().concat("SYMin"))));
					}
				}
				addAgentInfoRelaList.add(agentInfoRela);
			}
		}
		//代理信息表更新
		if(null != updateAgentInfoRela && updateAgentInfoRela.size() > 0){
			updateAgentInfoRelaList = new ArrayList<TradAgentInfoRela>();
			for (TradAgentInfo tradAgentInfo : updateAgentInfoRela) {
				TradAgentInfoRela agentInfoRela = new TradAgentInfoRela();
				agentInfoRela.setAgentInfoId(tradAgentInfo.getId());;
				agentInfoRela.setInsuPlanNo(tradAgentInfo.getPlanCode());
				agentInfoRela.setOperUser(user.getUserid());
				agentInfoRela.setOperTime(new Date());
				if("0332".equals(tradAgentInfo.getProdType())){
					if(null != batchImportMap.get(tradAgentInfo.getChannelCode().concat("JQMax"))){
						agentInfoRela.setNfeeProdMax(new BigDecimal((String)batchImportMap.get(tradAgentInfo.getChannelCode().concat("JQMax"))));
					}
					if(null != batchImportMap.get(tradAgentInfo.getChannelCode().concat("JQMin"))){
						agentInfoRela.setNfeeProdMin(new BigDecimal((String)batchImportMap.get(tradAgentInfo.getChannelCode().concat("JQMin"))));
					}
				}
				if("0360".equals(tradAgentInfo.getProdType())){
					if(null != batchImportMap.get(tradAgentInfo.getChannelCode().concat("SYMax"))){
						agentInfoRela.setNfeeProdMax(new BigDecimal((String)batchImportMap.get(tradAgentInfo.getChannelCode().concat("SYMax"))));
					}
					if(null != batchImportMap.get(tradAgentInfo.getChannelCode().concat("SYMin"))){
						agentInfoRela.setNfeeProdMin(new BigDecimal((String)batchImportMap.get(tradAgentInfo.getChannelCode().concat("SYMin"))));
					}
				}
				updateAgentInfoRelaList.add(agentInfoRela);
			}
			
		}

		int a = 0;
		int u = 0;
		if(null!=addAgentInfoRelaList && addAgentInfoRelaList.size() > 0){
			agentInfoRelaDao.insertIntoAgentRealForCar(addAgentInfoRelaList);
			a = addAgentInfoRelaList.size();
		}
		if(null!=updateAgentInfoRelaList && updateAgentInfoRelaList.size()>0){
			agentInfoRelaDao.updateAlreadyExistsCarInfo(updateAgentInfoRelaList);
			u = updateAgentInfoRelaList.size();
		}
		logger.info("插入车险费用信息  新增："+a+" 更新："+u);
		
	}
	private Map<String,Object> batchImportRuleCheck(HSSFSheet sheet,GatewayUser user) throws AppException {
 		StringBuffer errorMsg = new StringBuffer();
		int errorCount = 0;
		List<TradAgentInfo> temLs = new ArrayList<TradAgentInfo>();
		HashMap<String, Object> map = new HashMap<String, Object>();
		//当前登陆用户所属分公司两位机构代码
 		String departmentStr = user.getDepartmentStr().substring(1, 3);
 		RedisUtil redis = RedisUtil.getInstance();
		for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			// 1、首先筛选排除空白行或者不满足录入列条件的行数据
			if (hssfRow == null) {
				continue;
			}else if(19 > hssfRow.getLastCellNum()){
				errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行数据未导入，原因：列长度不满足业务需求规则，请检查数据或模板有更新请重新下载模板 <br>");
				errorCount++;
				continue;
			}
			Integer[] colNums={0,1,2,3,4,5,6,7,11,12,13,14,15,16,17,18};
			for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
				if(Arrays.asList(colNums).contains(colNum) && (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
					continue;
				}
			}
			String oldBnsType = ExcelUtil.getCellText(hssfRow.getCell(7)).trim();
			if(StrUtil.isNotEmpty(oldBnsType) && !oldBnsType.startsWith("19001")){
				if(StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(8)).trim())){
					errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，第 "+(9)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
				}
				if(StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(8)).trim())){
					Map<String, String> agentMap = redis.getValue(ExcelUtil.getCellText(hssfRow.getCell(8)).trim());
					if(null == agentMap || "0".equals(agentMap.get("validStatus"))){
						errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，第 "+(9)+" 列数据在销管系统不存在或已失效！请检查 <br>");
						errorCount++;
					}
				}
				if(StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(9)).trim())){
					errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，第 "+(10)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
				}
				
			}
			//校验导入出单机构代码：是否为登陆用户所属分公司下渠道信息
			if(!"00".equals(departmentStr) && !user.getDepartmentStr().contains(ExcelUtil.getCellText(hssfRow.getCell(3)).trim().substring(0,2))){
				errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，出单机构代码"+ExcelUtil.getCellText(hssfRow.getCell(3)).trim()+"不存在或没有相关权限，请检查修改<br>");
				errorCount++;
				continue;
			}
			if (ExcelUtil.getCellText(hssfRow.getCell(3)).trim().length() != 8) {
				errorMsg.append("总对总销售渠道信息第 ").append(rowNum + 1).append(" 行，出单机构代码").append(ExcelUtil.getCellText(hssfRow.getCell(3)).trim()).append("必须为8位<br>");
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			String branch = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
			String branchName = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
			String channelCode = ExcelUtil.getCellText(hssfRow.getCell(2)).trim();
			String dptCde = ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
			String channleCode = ExcelUtil.getCellText(hssfRow.getCell(4)).trim();
			String channleType = ExcelUtil.getCellText(hssfRow.getCell(5)).trim();
			String newBsnsType = ExcelUtil.getCellText(hssfRow.getCell(6)).trim();
			String categoryCode = ExcelUtil.getCellText(hssfRow.getCell(7)).trim();
			String agentNo = ExcelUtil.getCellText(hssfRow.getCell(8)).trim();
			String protocolNo = ExcelUtil.getCellText(hssfRow.getCell(9)).trim();
			String cbrkrname = ExcelUtil.getCellText(hssfRow.getCell(10)).trim();
			String serviceCode = ExcelUtil.getCellText(hssfRow.getCell(11)).trim();
			String serviceName = ExcelUtil.getCellText(hssfRow.getCell(12)).trim();
			String empNo = ExcelUtil.getCellText(hssfRow.getCell(13)).trim();
			String empName = ExcelUtil.getCellText(hssfRow.getCell(14)).trim();
			String teamCode = ExcelUtil.getCellText(hssfRow.getCell(15)).trim();
			String teamName = ExcelUtil.getCellText(hssfRow.getCell(16)).trim();
			String cAgentCode=ExcelUtil.getCellText(hssfRow.getCell(17)).trim();
			String coprcod = ExcelUtil.getCellText(hssfRow.getCell(18)).trim();
			String cSaleName=ExcelUtil.getCellText(hssfRow.getCell(19)).trim();
			String cSlsIdentItyNo=ExcelUtil.getCellText(hssfRow.getCell(20)).trim();
			String repairCode=ExcelUtil.getCellText(hssfRow.getCell(21)).trim();
			String repairName=ExcelUtil.getCellText(hssfRow.getCell(22)).trim();
			String cPhone=ExcelUtil.getCellText(hssfRow.getCell(23)).trim();
			String empPhone = ExcelUtil.getCellText(hssfRow.getCell(24)).trim();
			String JQ_Max = ExcelUtil.getCellText(hssfRow.getCell(25)).trim();
			String JQ_Min = ExcelUtil.getCellText(hssfRow.getCell(26)).trim();
			String SY_Max = ExcelUtil.getCellText(hssfRow.getCell(27)).trim();
			String SY_Min = ExcelUtil.getCellText(hssfRow.getCell(28)).trim();			
			TradAgentInfo temObj = new TradAgentInfo();
			temObj.setAgentType("4");
			temObj.setExcelRowIndex(rowNum+1);
			temObj.setSubChannelCode(branch);
			temObj.setRepairCode(repairCode);
			temObj.setRepairName(repairName);
			temObj.setBranch(branch);
			temObj.setBranchName(branchName);
			temObj.setChannelCode(channelCode);
			temObj.setDptCde(dptCde);
			temObj.setChannleCode(channleCode);
			temObj.setChannleType(channleType);
			temObj.setNewBsnsType(newBsnsType);
			temObj.setCategoryCode(categoryCode);
			temObj.setAgentNo(agentNo);
			temObj.setProtocolNo(protocolNo);
			temObj.setServiceCode(serviceCode);
			temObj.setServiceName(serviceName);
			temObj.setCbrkrname(cbrkrname);
			temObj.setCoprcod(coprcod);
			temObj.setEmpNo(empNo);
			temObj.setEmpName(empName);
			temObj.setTeamCode(teamCode);
			temObj.setTeamName(teamName);
			temObj.setOperUser(user.getUserid());
			temObj.setcAgentCode(cAgentCode);
			temObj.setcPhone(cPhone);
			temObj.setcSlsIdentItyNo(cSlsIdentItyNo);
			temObj.setcSaleName(cSaleName);
			temObj.setEmpPhone(empPhone);
			if(StrUtil.isNotEmpty(JQ_Max)){
				map.put(channelCode.concat(branch).concat("JQMax"), JQ_Max);
			}
			if(StrUtil.isNotEmpty(JQ_Min)){
				map.put(channelCode.concat(branch).concat("JQMin"), JQ_Min);
			}
			if(StrUtil.isNotEmpty(SY_Max)){
				map.put(channelCode.concat(branch).concat("SYMax"), SY_Max);
			}
			if(StrUtil.isNotEmpty(SY_Min)){
				map.put(channelCode.concat(branch).concat("SYMin"), SY_Min);
			}

			temLs.add(temObj);
		}
		map.put("AGENTLIST", temLs);
		//3、校验excel中的子机构代码不能重复
		int checkUserCodeErrorCount=0;
		for(int i = 0; i < temLs.size() - 1; i++){
			String checkSubChannelCode = temLs.get(i).getSubChannelCode();
			if(checkUserCodeErrorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			for(int j = i + 1; j < temLs.size(); j++){
				if(checkSubChannelCode.equals(temLs.get(j).getSubChannelCode())){
					errorMsg.append("总对总销售渠道信息 第  "+(i+3)+" 行与第 "+(j+3)+" 行合作伙伴、区域代码、网点代码列组成的唯一标识重复，请修改 <br>");
					checkUserCodeErrorCount++;
					continue;
				}
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		return map;
	}

	@Override
	public List<TradAgentInfoRela> queryCarAgentRelaInfo(TradAgentInfoRela agentInfoRela)
			throws Exception {
		return agentInfoRelaDao.queryTradAgentInfoRelaByMap(agentInfoRela);
	}

	@Override
	public Page<CoreDept> queryCoreDeptForCar(Page<CoreDept> page,
			String departmentStr) throws Exception {
		return  deptDao.queryCoreDeptForCar(page,departmentStr);
	}

	@Override
	public List<TradAgentInfo> queryTradAgentInfo(TradAgentInfo agentInfo)
			throws Exception {
		return agentInfoMtDao.queryAgentInfoByMap(agentInfo);
	}

	@Override
	public boolean internalTradInfoaddorupdate(TradAgentInfo agentInfo,String internalAgentId, String carAgentId,String carSubChannelCode) throws Exception {
		boolean flag = false;
		agentInfo.setSubChannelCode(carSubChannelCode+"_CY");
		TradAgentInfo isTradAgentInfo = agentInfoMtDao.queryAgentInfoByProductType(agentInfo);
		if(StrUtil.isNotEmpty(internalAgentId) && isTradAgentInfo != null){
			agentInfo.setId(isTradAgentInfo.getId());
			flag =agentInfoMtDao.updateAgentInfo(agentInfo);
		}else{
			TradAgentInfo tradAgentInfo = new TradAgentInfo();
			tradAgentInfo.setId(Long.parseLong(carAgentId));
			tradAgentInfo.setKey1(carSubChannelCode+"_CY");
			flag = agentInfoMtDao.updateAgentInfo(tradAgentInfo);
			if(flag){
				agentInfo.setAgentType("4");
				flag = agentInfoMtDao.addAgentInfo(agentInfo);
			}
		}
		return flag;
	}

	private Map<String,String> agentInfoValidation(TradAgentInfo tradAgentInfo) throws Exception {
		System.out.println(tradAgentInfo);
		Map<String,Object> map = new HashMap<String, Object>();
		Map<String,String> resultMap = new HashMap<String, String>();
		SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
		String code = sf.format(new Date());
		map.put("SERIALNUMBER",code);
		map.put("tradAgentInfo",tradAgentInfo);
		List<DictDto> newBusinessSource = DictCache.getDict("new_business_source");
		String agentType = getAgentTypeByNewBsnsType(tradAgentInfo.getNewBsnsType(),newBusinessSource);
		map.put("AGENTTYPE",agentType);
		map = getAgentPermitn(agentType, tradAgentInfo.getcAgentCode(),tradAgentInfo.getDptCde(),map);
		map.put("channleType",tradAgentInfo.getChannleType().substring(0,1));
		String xml = "";
		String responseXml = ""; //交易系统返回结果报文
		xml = FreeMarkerUtil.process("/app/saleESB/30005.ftl",map); //转化后的报文
		logger.debug("查询交易系统的报文:"+xml);
		responseXml = HttpConnectorUtil.interaction(Config.getProp("salemanage.check.url"),xml,tradAgentInfo.getChannelCode(), Constants.GW_CH_TX_30005);
		logger.debug("交易系统返回报文"+responseXml);
		Document document = DocumentHelper.parseText(responseXml);
		Element root = document.getRootElement();
		String resultCode = selectSingleNodeByRoot(root , "/XML/HEAD/RESMSGCODE");
		if ("0000".equals(resultCode)) {
			resultMap.put("result",resultCode);
		}else {
			//校验有错
			List<Element> body = root.selectNodes("/XML/BODY");
			String result = "";
			for (Element element : body) {
				Iterator<Element> iterator = element.elementIterator();
				while (iterator.hasNext()){
					String errorMsg = iterator.next().getText();
					if (!"1".equals(errorMsg) && !"0".equals(errorMsg)){
						result = result + errorMsg + "<br>";
					}
				}
			}
			resultMap.put("result",result);
		}
		return resultMap;
	}

	public String selectSingleNodeByRoot(Element root,String path){
		Node node = root.selectSingleNode(path);
		if(node==null){
			return "";
		}else{
			return node.getText();
		}
	}
	private String getAgentTypeByNewBsnsType(String newBsnsType,List<DictDto> newBusinessSource) {
		String agentType = "";
		for (DictDto dictDto : newBusinessSource) {
			if (newBsnsType.equals(dictDto.getCode())){
				if ("1900101".equals(dictDto.getOtherCode2())){
					agentType = "0";
					break;
				}else if ("1900203".equals(dictDto.getOtherCode2())) {
					agentType = "1";
					break;
				}else if ("1900201".equals(dictDto.getOtherCode2())) {
					agentType = "2";
					break;
				}else if ("1900202".equals(dictDto.getOtherCode2())) {
					agentType = "3";
					break;
				}else if ("1900301".equals(dictDto.getOtherCode2())) {
					agentType = "4";
					break;
				}
			}
		}
		return agentType;
	}

	//根据代理类型查看车险的中介机构许可证号
	public Map<String,Object> getAgentPermitn(String agentType, String cAgentCode, String dptCode, Map<String,Object> map) {
		if ("2".equals(agentType) || "3".equals(agentType) || "4".equals(agentType)){
			//专兼经业务
			if ("32".equals(dptCode.substring(0,2)) || "86".equals(dptCode.substring(0,2)) || "87".equals(dptCode.substring(0,2)) || "33".equals(dptCode.substring(0,2))){
				//（江苏、苏州、浙江）地区
				map.put("AGENT_PERMITNOF",cAgentCode);
			}else {
				//非（江苏、苏州、浙江）地区
				map.put("AGENT_PERMITNOD",cAgentCode);
			}
		}else {
			//非专兼经业务
			map.put("AGENT_PERMITNOB",cAgentCode);
		}
		return map;
	}

}
